package com.gitee.magic.jdbc.persistence.source.jdbc;

import java.io.Serializable;
import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import com.gitee.magic.context.PropertyConverterEditor;
import com.gitee.magic.core.converter.AbstractConverterEditor;
import com.gitee.magic.core.utils.ReflectUtils;
import com.gitee.magic.core.utils.StringUtils;
import com.gitee.magic.jdbc.persistence.EntityObject;
import com.gitee.magic.jdbc.persistence.EntityProperty;
import com.gitee.magic.jdbc.persistence.RepositoryException;
import com.gitee.magic.jdbc.persistence.annotation.GeneratedValue;
import com.gitee.magic.jdbc.persistence.source.AbstractEntityManager;
import com.gitee.magic.jdbc.persistence.source.jdbc.sqlplus.conditions.SqlWrapperBuilder;
import com.gitee.magic.jdbc.persistence.source.jdbc.sqlplus.conditions.query.QueryWrapper;
import com.gitee.magic.jdbc.persistence.source.jdbc.sqlplus.conditions.update.DeleteWrapper;
import com.gitee.magic.jdbc.persistence.source.jdbc.sqlplus.conditions.update.UpdateWrapper;

/**
 * 实体管理器
 * 
 * @author Start
 */
public class EntityJdbcManager extends AbstractEntityManager {
	
	private static final String INSERT="Insert Into %s(%s) Values(%s)";
	private static final String UPDATE="Update %s Set %s Where %s=?";
	
	private JdbcTemplate jdbcTemplate;
	
	public EntityJdbcManager(){
		
	}
	
	public EntityJdbcManager(DataSource dataSource){
		jdbcTemplate=new JdbcTemplate(dataSource);
	}
	
	/**
	 * 批量插入对象
	 * @param prototype
	 * @param entitys
	 * @return
	 */
	public <T>int[] persistBatch(Class<T> prototype,List<T> entitys) {
		EntityObject eObject=getEntityDefinition(prototype);
		//生成批量固定语句
		List<String> fieldNames = new ArrayList<String>();
		List<String> positions = new ArrayList<String>();
		if (eObject.getEntityPropertyStubPrimaryKey().getGeneratedValue() == GeneratedValue.NONE||
				eObject.getEntityPropertyStubPrimaryKey().getGeneratedValue() == GeneratedValue.UID) {
			fieldNames.add(eObject.getEntityPropertyStubPrimaryKey().getTableFieldName());
			positions.add("?");
		}
		for (EntityProperty member : eObject.getMembers()) {
			fieldNames.add(member.getTableFieldName());
			positions.add("?");
		}
		String sql = String.format(INSERT, eObject.getTableName(),
				StringUtils.listToString(fieldNames), StringUtils.listToString(positions));
		//参数
		List<Object[]> params = new ArrayList<>();
		for(T entity:entitys) {
			List<Object> parameters=new ArrayList<>();
			if (eObject.getEntityPropertyStubPrimaryKey().getGeneratedValue() == GeneratedValue.NONE||
					eObject.getEntityPropertyStubPrimaryKey().getGeneratedValue() == GeneratedValue.UID) {
				if (eObject.getEntityPropertyStubPrimaryKey().getGeneratedValue() == GeneratedValue.UID) {
					try {
						// 如果为UID生成策略则自动生成一个UID值
						eObject.getEntityPropertyStubPrimaryKey().getDescriptor().getWriteMethod().invoke(entity, StringUtils.getUuid());
					} catch (IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {
						throw new RepositoryException(e);
					}
				}
				// 获取已经设置的主键值
				Object primaryKeyValue = null;
				try {
					primaryKeyValue = eObject.getEntityPropertyStubPrimaryKey().getDescriptor().getReadMethod().invoke(entity);
				} catch (IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {
					throw new RepositoryException(e);
				}
				// 主键字段
				parameters.add(PropertyConverterEditor.out(prototype,eObject.getEntityPropertyStubPrimaryKey().getFieldName(),eObject.getEntityPropertyStubPrimaryKey().getDataType(),primaryKeyValue));
			}
			// 其它字段
			for (EntityProperty member : eObject.getMembers()) {
				Object value=null;
				try {
					value = member.getDescriptor().getReadMethod().invoke(entity);
				} catch (IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {
					throw new RepositoryException(e);
				}
				parameters.add(PropertyConverterEditor.out(prototype,member.getFieldName(),member.getDataType(),value));
			}
			params.add(parameters.toArray());
		}
		return executeBatch(sql.toString(),params);
	}

	/**
	 * 批量更新对象
	 * @param prototype
	 * @param entitys
	 * @return
	 */
	public <T>int[] mergeBatch(Class<T> prototype,List<T> entitys) {
		EntityObject eObject=getEntityDefinition(prototype);
		List<String> setFields = new ArrayList<>();
		for (EntityProperty member : eObject.getMembers()) {
			setFields.add(member.getTableFieldName()+"=?");
		}
		String sql = String.format(UPDATE, eObject.getTableName(),
				StringUtils.listToString(setFields), eObject.getEntityPropertyStubPrimaryKey().getTableFieldName());
		List<Object[]> params = new ArrayList<>();
		for(T entity:entitys) {
			List<Object> parameters=new ArrayList<>();
			for (EntityProperty member : eObject.getMembers()) {
				Object value=null;
				try {
					value = member.getDescriptor().getReadMethod().invoke(entity);
				} catch (IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {
					throw new RepositoryException(e);
				}
				parameters.add(PropertyConverterEditor.out(prototype,member.getFieldName(),member.getDataType(),value));
			}
			parameters.add(getPrimaryKeyValue(prototype, eObject, entity));
			params.add(parameters.toArray());
		}
		return executeBatch(sql.toString(),params);
	}
	
	public int persist(Object entity,Boolean isSkipNull) {
		Class<?> prototype=entity.getClass();
		EntityObject eObject=getEntityDefinition(prototype);
		List<String> fieldNames = new ArrayList<String>();
		List<String> positions = new ArrayList<String>();
		List<Object> parameters = new ArrayList<Object>();
		List<String> duplicateKeyUpdatedFieldList=new ArrayList<>();
		
		if (eObject.getEntityPropertyStubPrimaryKey().getGeneratedValue() == GeneratedValue.NONE||
				eObject.getEntityPropertyStubPrimaryKey().getGeneratedValue() == GeneratedValue.UID) {
			if (eObject.getEntityPropertyStubPrimaryKey().getGeneratedValue() == GeneratedValue.UID) {
				try {
					// 如果为UID生成策略则自动生成一个UID值
					eObject.getEntityPropertyStubPrimaryKey().getDescriptor().getWriteMethod().invoke(entity, StringUtils.getUuid());
				} catch (IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {
					throw new RepositoryException(e);
				}
			}
			// 获取已经设置的主键值
			Object primaryKeyValue = null;
			try {
				primaryKeyValue = eObject.getEntityPropertyStubPrimaryKey().getDescriptor().getReadMethod().invoke(entity);
			} catch (IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {
				throw new RepositoryException(e);
			}
			// 主键字段
			if (!StringUtils.isEmpty(primaryKeyValue)) {
				fieldNames.add(eObject.getEntityPropertyStubPrimaryKey().getTableFieldName());
				positions.add("?");
				parameters.add(PropertyConverterEditor.out(prototype,eObject.getEntityPropertyStubPrimaryKey().getFieldName(),eObject.getEntityPropertyStubPrimaryKey().getDataType(),primaryKeyValue));
			}
		}
		// 其它字段
		for (EntityProperty member : eObject.getMembers()) {
			Object value=null;
			try {
				value = member.getDescriptor().getReadMethod().invoke(entity);
			} catch (IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {
				throw new RepositoryException(e);
			}
			// 为空则不插入
			if (value == null) {
				if(isSkipNull) {
					continue;
				}
			}
			fieldNames.add(member.getTableFieldName());
			positions.add("?");
			parameters.add(PropertyConverterEditor.out(prototype,member.getFieldName(),member.getDataType(),value));
			if(!StringUtils.isEmpty(member.getDuplicateKeyUpdated())) {
				duplicateKeyUpdatedFieldList.add(member.getFieldName()+"="+member.getDuplicateKeyUpdated());
			}
		}
		final String sql = String.format(INSERT, eObject.getTableName(),
				StringUtils.listToString(fieldNames), StringUtils.listToString(positions));
		if (eObject.getEntityPropertyStubPrimaryKey().getGeneratedValue() == GeneratedValue.AUTO) {
			KeyHolder keyHolder = new GeneratedKeyHolder();
		    int returnValue=getJdbcTemplate().update(new PreparedStatementCreator() {

				@Override
				public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
					PreparedStatement preparedStatement = con.prepareStatement(sql, new String[]{eObject.getEntityPropertyStubPrimaryKey().getFieldName()});
					for(int i=0;i<parameters.size();i++	) {
						preparedStatement.setObject(i+1,parameters.get(i));
					}
					return preparedStatement;
				}
		    	
		    }, keyHolder);
		    try {
				eObject.getEntityPropertyStubPrimaryKey().getDescriptor().getWriteMethod().invoke(entity, keyHolder.getKey().longValue());
			} catch (IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {
				throw new RepositoryException(e);
			}
		    return returnValue;
		}else {
			StringBuilder sqlStr=new StringBuilder();
			sqlStr.append(sql);
			if(!duplicateKeyUpdatedFieldList.isEmpty()) {
				sqlStr.append(" ON DUPLICATE KEY UPDATE ");
				sqlStr.append(StringUtils.listToString(duplicateKeyUpdatedFieldList));
			}
			return executeUpdate(sqlStr.toString(), parameters.toArray());
		}
	}

	public int merge(Object entity,Boolean isSkipNull) {
		Class<?> prototype=entity.getClass();
		EntityObject eObject=getEntityDefinition(prototype);
		Object primaryKeyValue=getPrimaryKeyValue(prototype, eObject, entity);
		UpdateWrapper wrapper=new UpdateWrapper();
		wrapper.eq(eObject.getEntityPropertyStubPrimaryKey().getFieldName(), primaryKeyValue);
		for (EntityProperty member : eObject.getMembers()) {
			Object value=null;
			try {
				value = member.getDescriptor().getReadMethod().invoke(entity);
			} catch (IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {
				throw new RepositoryException(e);
			}
			// 为空则不插入
			if (value == null) {
				if(isSkipNull) {
					continue;
				}
			}
			wrapper.set(member.getTableFieldName(),
					PropertyConverterEditor.out(prototype,member.getFieldName(),member.getDataType(),value));
		}
		return executeUpdate(SqlWrapperBuilder.getSql(eObject.getTableName(), wrapper)
				,SqlWrapperBuilder.getParams(wrapper));
	}

	public int remove(Object entity) {
		Class<?> prototype=entity.getClass();
		EntityObject eObject=getEntityDefinition(prototype);
		Object primaryKeyValue=getPrimaryKeyValue(prototype, eObject, entity);
		DeleteWrapper wrapper=new DeleteWrapper();
		wrapper.eq(eObject.getEntityPropertyStubPrimaryKey().getFieldName(), primaryKeyValue);
		return executeUpdate(SqlWrapperBuilder.getSql(eObject.getTableName(), wrapper)
				,SqlWrapperBuilder.getParams(wrapper));
	}

	public <T> T load(Class<T> prototype, Serializable primaryKeyValue) {
		EntityObject eObject=getEntityDefinition(prototype);
		//查询字段
		List<String> fieldList=new ArrayList<>();
		fieldList.add(eObject.getEntityPropertyStubPrimaryKey().getTableFieldName());
		for (EntityProperty member : eObject.getMembers()) {
			fieldList.add(member.getTableFieldName());
		}
		String[] columnNames=new String[fieldList.size()];
		fieldList.toArray(columnNames);
		//主键
		Object keyValue = PropertyConverterEditor.out(prototype,eObject.getEntityPropertyStubPrimaryKey().getFieldName(),
				eObject.getEntityPropertyStubPrimaryKey().getDataType(),primaryKeyValue);
		QueryWrapper wrapper=new QueryWrapper();
		wrapper.select(columnNames);
		wrapper.eq(eObject.getEntityPropertyStubPrimaryKey().getTableFieldName(), keyValue);
        wrapper.last("limit 1");
		List<T> entitys = getListEntityBySql(prototype, 
				SqlWrapperBuilder.getSql(eObject.getTableName(), wrapper)
				,SqlWrapperBuilder.getParams(wrapper));
		if (entitys.size() >0) {
			return entitys.get(0);
		} else {
			return null;
		}
	}
	
	public <T> List<T> getListEntityBySql(Class<T> prototype, String sql, Object... params) {
		List<Map<String,  Object>> entitys = getListMapBySql(sql, params);
		List<T> tEntitys = new ArrayList<>();
		if (entitys.isEmpty()) {
			return tEntitys;
		}
		// 把List,Map组成装对象
		EntityObject eObject=getEntityDefinition(prototype);
		for (Map<String, Object> en : entitys) {
			T instance=ReflectUtils.newInstance(prototype);
			Object value=en.get(eObject.getEntityPropertyStubPrimaryKey().getTableFieldName());
			// 获取已经设置的主键值
			setEntityValue(instance,prototype,value,eObject.getEntityPropertyStubPrimaryKey());
			// 类字段成员
			for (EntityProperty member : eObject.getMembers()) {
				setEntityValue(instance,prototype,en.get(member.getTableFieldName()),member);
			}
			tEntitys.add((T) instance);
		}
		return tEntitys;
	}

	@SuppressWarnings("unchecked")
	public <T> List<T> getListEditorBySql(Class<T> prototype, String sql, Object... params) {
		AbstractConverterEditor<?> editor=PropertyConverterEditor.getBasicConverter(prototype);
		if(editor==null) {
			throw new NullPointerException("不支持"+prototype+",转写器类型");
		}
		List<Map<String,  Object>> entitys = getListMapBySql(sql, params);
		List<T> tEntitys = new ArrayList<>();
		if (entitys.isEmpty()) {
			return tEntitys;
		}
		for (Map<String, Object> en : entitys) {
			for(Map.Entry<String, Object> obj:en.entrySet()) {
				//只取第一个查询的列
				editor.restore(obj.getValue());
				tEntitys.add((T)editor.getValue());
				break;
			}
		}
		return tEntitys;
	}

	public List<Map<String, Object>> getListMapBySql(String sql, Object... params) {
		return getJdbcTemplate().queryForList(sql, params);
	}
	
	public int executeUpdate(String sql, Object... params){
		return getJdbcTemplate().update(sql,params);
	}
	
	public int[] executeBatch(String sql,List<Object[]> params){
		return getJdbcTemplate().batchUpdate(sql,params);
	}
	
	//////////////////////////////////////////////////////////////// end
	//////////////////////////////////////////////////////////////// select/////////////////////////////////////////////////////////////////////
	
	/**
	 * spring JDBC操作类
	 */
	public JdbcTemplate getJdbcTemplate() {
		return this.jdbcTemplate;
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

}